﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using BullTasDesaDal.Entities;
using NHibernate.Criterion;
using NHibernate.SqlCommand;
using System.Data;

namespace BullTasDesaDal.Service
{
    public class AppraisalService
    {
        public static void Save(Appraisal appraisal)
        {
            using (NHibernate.ISession session = SessionFactory.GetNewSession())
            {
                using (NHibernate.ITransaction transaction = session.BeginTransaction())
                {
                    session.SaveOrUpdate(appraisal);
                    transaction.Commit();
                    
                    
                }
            }
        }


        //public static int Save(Appraisal appraisal)
        //{
        //    using (NHibernate.ISession session = SessionFactory.GetNewSession())
        //    {
        //        using (NHibernate.ITransaction transaction = session.BeginTransaction())
        //        {
                    
        //            transaction.Commit();


        //        }
        //    }
        //}


        public static IList<Appraisal> GetAll(short? idProvince, int? idDepartment, string idRegion, string idZone, string establishmentName)
        {
            using (NHibernate.ISession session = SessionFactory.GetNewSession())
            {
                NHibernate.ICriteria criteria = session.CreateCriteria(typeof(Appraisal));
                //Locality
                criteria.CreateAlias("Locality", "locality", JoinType.InnerJoin);
                criteria.CreateAlias("locality.Department", "lDepartment", JoinType.InnerJoin);
                criteria.CreateAlias("lDepartment.Province", "dProvince", JoinType.InnerJoin);
                //LocalityPC
                criteria.CreateAlias("LocalityPC", "localityPC", JoinType.InnerJoin);
                criteria.CreateAlias("localityPC.Department", "lpcDepartment", JoinType.InnerJoin);
                criteria.CreateAlias("lpcDepartment.Province", "dpcProvince", JoinType.InnerJoin);
                //LocalityPL
                criteria.CreateAlias("LocalityPL", "localityPL", JoinType.InnerJoin);
                criteria.CreateAlias("localityPL.Department", "lplDepartment", JoinType.InnerJoin);
                criteria.CreateAlias("lplDepartment.Province", "dplProvince", JoinType.InnerJoin);
                //Zone
                criteria.CreateAlias("Zone", "zone", JoinType.InnerJoin);
                criteria.CreateAlias("zone.Region", "zRegion", JoinType.InnerJoin);

                if (idProvince != null)
                    criteria.Add(Expression.Eq("dProvince.Id", idProvince));
                if (idDepartment != null)
                    criteria.Add(Expression.Eq("lDepartment.Id", idDepartment));
                if (!string.IsNullOrEmpty(idRegion))
                    criteria.Add(Expression.Eq("zRegion.Id", idRegion));
                if (!string.IsNullOrEmpty(idZone))
                    criteria.Add(Expression.Eq("zone.Id", idZone));
                if (!string.IsNullOrEmpty(establishmentName))
                    criteria.Add(Expression.Like("EstablishmentName", "%" + establishmentName + "%"));
                criteria.AddOrder(Order.Asc("DateCreate"));

                return criteria.List<Appraisal>();


            }
        }

        public static Appraisal GetById(int idAppraisal)
        {
            using (NHibernate.ISession session = SessionFactory.GetNewSession())
            {
                NHibernate.ICriteria criteria = session.CreateCriteria(typeof(Appraisal));
                //Locality
                criteria.CreateAlias("Locality", "locality", JoinType.InnerJoin);
                criteria.CreateAlias("locality.Department", "lDepartment", JoinType.InnerJoin);
                criteria.CreateAlias("lDepartment.Province", "dProvince", JoinType.InnerJoin);
                //LocalityPC
                criteria.CreateAlias("LocalityPC", "localityPC", JoinType.InnerJoin);
                criteria.CreateAlias("localityPC.Department", "lpcDepartment", JoinType.InnerJoin);
                criteria.CreateAlias("lpcDepartment.Province", "dpcProvince", JoinType.InnerJoin);
                //LocalityPL
                criteria.CreateAlias("LocalityPL", "localityPL", JoinType.InnerJoin);
                criteria.CreateAlias("localityPL.Department", "lplDepartment", JoinType.InnerJoin);
                criteria.CreateAlias("lplDepartment.Province", "dplProvince", JoinType.InnerJoin);
                //Zone
                criteria.CreateAlias("Zone", "zone", JoinType.InnerJoin);
                criteria.CreateAlias("zone.Region", "zRegion", JoinType.InnerJoin);

                criteria.Add(Restrictions.Eq("Id", idAppraisal));

                return criteria.UniqueResult<Appraisal>();


            }
        }


        public static DataTable GetTable(short? idProvince, int? idDepartment, string idRegion, string idZone, string establishmentName)
        {
            using (NHibernate.ISession session = SessionFactory.GetNewSession())
            {
                NHibernate.ICriteria criteria = session.CreateCriteria(typeof(Appraisal));
                //Locality
                criteria.CreateAlias("Locality", "locality", JoinType.InnerJoin);
                criteria.CreateAlias("locality.Department", "lDepartment", JoinType.InnerJoin);
                criteria.CreateAlias("lDepartment.Province", "dProvince", JoinType.InnerJoin);
                //LocalityPC
                criteria.CreateAlias("LocalityPC", "localityPC", JoinType.InnerJoin);
                criteria.CreateAlias("localityPC.Department", "lpcDepartment", JoinType.InnerJoin);
                criteria.CreateAlias("lpcDepartment.Province", "dpcProvince", JoinType.InnerJoin);
                //LocalityPL
                criteria.CreateAlias("LocalityPL", "localityPL", JoinType.InnerJoin);
                criteria.CreateAlias("localityPL.Department", "lplDepartment", JoinType.InnerJoin);
                criteria.CreateAlias("lplDepartment.Province", "dplProvince", JoinType.InnerJoin);
                //Zone
                criteria.CreateAlias("Zone", "zone", JoinType.InnerJoin);
                criteria.CreateAlias("zone.Region", "zRegion", JoinType.InnerJoin);

                if (idProvince != null)
                    criteria.Add(Expression.Eq("dProvince.Id", idProvince));
                if (idDepartment != null)
                    criteria.Add(Expression.Eq("lDepartment.Id", idDepartment));
                if (!string.IsNullOrEmpty(idRegion))
                    criteria.Add(Expression.Eq("zRegion.Id", idRegion));
                if (!string.IsNullOrEmpty(idZone))
                    criteria.Add(Expression.Eq("zone.Id", idZone));
                if (!string.IsNullOrEmpty(establishmentName))
                    criteria.Add(Expression.Like("EstablishmentName", "%" + establishmentName + "%"));

                return Common.ConvertTo<Appraisal>(criteria.List<Appraisal>());


            }
        }

        public static IList<Appraisal> GetAll(Filter filter)
        {
            using (NHibernate.ISession session = SessionFactory.GetNewSession())
            {
                NHibernate.ICriteria criteria = session.CreateCriteria(typeof(Appraisal));
                //Locality
                criteria.CreateAlias("Locality", "locality", JoinType.InnerJoin);
                criteria.CreateAlias("locality.Department", "lDepartment", JoinType.InnerJoin);
                criteria.CreateAlias("lDepartment.Province", "dProvince", JoinType.InnerJoin);
                //LocalityPC
                criteria.CreateAlias("LocalityPC", "localityPC", JoinType.InnerJoin);
                criteria.CreateAlias("localityPC.Department", "lpcDepartment", JoinType.InnerJoin);
                criteria.CreateAlias("lpcDepartment.Province", "dpcProvince", JoinType.InnerJoin);
                //LocalityPL
                criteria.CreateAlias("LocalityPL", "localityPL", JoinType.InnerJoin);
                criteria.CreateAlias("localityPL.Department", "lplDepartment", JoinType.InnerJoin);
                criteria.CreateAlias("lplDepartment.Province", "dplProvince", JoinType.InnerJoin);
                //Zone
                criteria.CreateAlias("Zone", "zone", JoinType.InnerJoin);
                criteria.CreateAlias("zone.Region", "zRegion", JoinType.InnerJoin);

                if (filter.IdProvince != null)
                    criteria.Add(Expression.Eq("dProvince.Id", filter.IdProvince));
                if (filter.IdDepartment != null)
                    criteria.Add(Expression.Eq("lDepartment.Id", filter.IdDepartment));
                if (!string.IsNullOrEmpty(filter.IdRegion))
                    criteria.Add(Expression.Eq("zRegion.Id", filter.IdRegion));
                if (!string.IsNullOrEmpty(filter.IdZone))
                    criteria.Add(Expression.Eq("zone.Id", filter.IdZone));
                if (!string.IsNullOrEmpty(filter.EstablishmentName))
                    criteria.Add(Expression.Like("EstablishmentName", "%" + filter.EstablishmentName + "%"));
                if (filter.FromDate!=null)
                    criteria.Add(Expression.Ge("DateCreate", filter.FromDate));
                if (filter.ToDate != null)
                    criteria.Add(Expression.Le("DateCreate", filter.ToDate));

                return criteria.List<Appraisal>();


            }

        }

        public static IList<Revisor> GetAllRevisors()
        {
            using (NHibernate.ISession session = SessionFactory.GetNewSession())
            {
                return session.CreateCriteria(typeof(Revisor))
                    .List<Revisor>();


            }
        }
    }
}